package com.class1group3.demo;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class HiveTableInitializer implements CommandLineRunner {

    private final JdbcTemplate jdbcTemplate;

    public HiveTableInitializer(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void run(String... args) throws Exception {
        // 1. 创建结果表（如果不存在）
        jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS book_rating_summary (" +
                "rating INT COMMENT '评分等级(1-5星)', " +
                "total_ratings BIGINT COMMENT '该等级总评分数', " +
                "percentage DECIMAL(5,2) COMMENT '占比百分比'" +
                ") STORED AS ORC");

        // 2. 使用 INSERT OVERWRITE 替代 TRUNCATE + INSERT
        jdbcTemplate.execute(
                "INSERT OVERWRITE TABLE book_rating_summary " +
                        "SELECT " +
                        "    rating_level AS rating, " +
                        "    SUM(rating_count) AS total_ratings, " +
                        "    ROUND(SUM(rating_count) * 100.0 / total.total_count, 2) AS percentage " +
                        "FROM ( " +
                        "    SELECT " +
                        "        rating_info.col1 AS rating_level, " +
                        "        CAST(SPLIT(rating_info.col2, ':')[1] AS INT) AS rating_count " +
                        "    FROM books " +
                        "    LATERAL VIEW explode(array( " +
                        "        struct(1, rating_dist1), " +
                        "        struct(2, rating_dist2), " +
                        "        struct(3, rating_dist3), " +
                        "        struct(4, rating_dist4), " +
                        "        struct(5, rating_dist5) " +
                        "    )) t AS rating_info " +
                        ") ratings " +
                        "CROSS JOIN ( " +
                        "    SELECT SUM(CAST(SPLIT(rating_dist, ':')[1] AS INT)) AS total_count " +
                        "    FROM books " +
                        "    LATERAL VIEW explode(array( " +
                        "        rating_dist1, rating_dist2, rating_dist3, rating_dist4, rating_dist5 " +
                        "    )) t AS rating_dist " +
                        ") total " +
                        "GROUP BY rating_level, total.total_count " +
                        "ORDER BY rating_level DESC"
        );
    }
}